﻿CREATE PROCEDURE [dbo].[UpdateOrders]
WITH EXECUTE AS CALLER
AS
BEGIN
	SET NOCOUNT ON;
begin tran

truncate table PR.SupplierPrices

	if (@@error<>0)
	begin 
		rollback 
		return 1
	end

insert into PR.SupplierPrices(GoodsId, Price)
select il.GoodsId, il.SupplierPrice
from CD.InputLines il inner join 
(select  GoodsId,  max(Id) as Id
from CD.InputLines
group by GoodsId) as lg
on il.Id = lg.Id

	if (@@error<>0)
	begin 
		rollback 
		return 1
	end

truncate table PR.SuplierDate

	if (@@error<>0)
	begin 
		rollback 
		return 1
	end

insert into PR.SuplierDate(GoodsId, SDate)
select  il.GoodsId, max(d.DocDate)
from CD.InputLines il inner join
CA.Documents d on d.Id = il.DocId 
inner join 
(select i.GoodsId , max(d1.Id) as DId
from CD.InputLines i inner join CA.Documents d1
on i.DocId = d1.Id 
group by i.GoodsId) as tmp 
on il.GoodsId = tmp.GoodsId and d.Id = tmp.DId
group by il.GoodsId

	if (@@error<>0)
	begin 
		rollback 
		return 1
	end

commit tran

EXEC sp_MSforeachtable @command1 = 'DBCC DBREINDEX ("?")'
EXEC sp_MSforeachtable @command1 = 'UPDATE STATISTICS ? WITH FULLSCAN, ALL'

END
